using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace System.Resources.SqlResource
{
    /// <summary>
    /// Read data from SQL Server
    /// </summary>
    public class SqlResourceGetDataSql2000  :ISqlResourceGetData
    {
        private Web.Caching.CacheItemRemovedCallback onRemove;
        private readonly string _cnString = System.Configuration.ConfigurationManager.ConnectionStrings["cnResourceSql"].ConnectionString;

        public SqlResourceGetDataSql2000()
        {
            if (System.Web.HttpContext.Current.Cache["Rebuild_ResourceSet"] == null)
            {
                try
                {
                    if (System.Web.Caching.SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(_cnString).Length == 0)
                    {
                        System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(_cnString, "tbl_ResourceSQL");
                    }
                }
                catch (Exception ex)
                {
                    Exception err = new Exception("->>Run Reg.cmd batch.<<- Or verify Connection." + ex.Message);
                    throw err;
                }
                SetDTinCache();
            }
            
        }

        public string RebuildResourceSet
        {
            get 
            {
                if (System.Web.HttpContext.Current.Cache["Rebuild_ResourceSet"] != null)
                { 
                    return (string)System.Web.HttpContext.Current.Cache["Rebuild_ResourceSet"]; 
                }
                else
                {
                    //setDTinCache();
                    //return (string)System.Web.HttpContext.Current.Cache["Rebuild_ResourceSet"]; 
                    return null;
                }
            }
        }

        public DataTable GetResources(Globalization.CultureInfo ci)
        {
            System.Diagnostics.Trace.WriteLine("getResources -> Get All Resources");
            List<SqlParameter> list_param = new List<SqlParameter>();
            SqlParameter p;

            p = new SqlParameter("@id_Globalization", SqlDbType.VarChar);
            p.Value = ci.Name;
            list_param.Add(p);

            return getDataDt("dbo.getResources", list_param);
        }

        public void SetResource(Globalization.CultureInfo ci, string key, string valueString, byte[] valueObj, string type)
        {
            if ((valueString != null) && (valueObj != null))
            {
                Exception ex = new Exception("Please verify parameter value. Only one parameter value can valorized.");
                throw ex;
            }

            if ((valueString == null) && (valueObj == null))
            {
                Exception ex = new Exception("Please verify parameter value. Only one parameter value can valorized.");
                throw ex;
            }

            List<SqlParameter> list_param = new List<SqlParameter>();
            SqlParameter p;

            p = new SqlParameter("@id_Globalization", SqlDbType.VarChar);
            p.Value = ci.Name;
            list_param.Add(p);

            p = new SqlParameter("@ParamName", SqlDbType.VarChar);
            p.Value = key;
            list_param.Add(p);

            p = new SqlParameter("@ParamStringValue", SqlDbType.Text);
            p.Value = valueString;
            list_param.Add(p);

            p = new SqlParameter("@ParamObjectValue", SqlDbType.Image);
            p.Value = valueObj;
            list_param.Add(p);

            p = new SqlParameter("@ParamType", SqlDbType.VarChar);
            p.Value = type;
            list_param.Add(p);

            execStored("dbo.setResource", list_param);
        }

        public void DelResource(Globalization.CultureInfo ci, string key)
        {
            List<SqlParameter> list_param = new List<SqlParameter>();
            SqlParameter p;

            p = new SqlParameter("@id_Globalization", SqlDbType.VarChar);
            p.Value = ci.Name;
            list_param.Add(p);

            p = new SqlParameter("@ParamName", SqlDbType.VarChar);
            p.Value = key;
            list_param.Add(p);

            execStored("dbo.delResource", list_param);
        }

        private void SetDTinCache()
        {
            System.Diagnostics.Trace.WriteLine("setDTinCache -> Cache Enabled.");
            onRemove = this.RemovedCallback;
            string _Rebuild_ResourceSet = "GUID_" + Guid.NewGuid();
            try
            {
                Web.Caching.SqlCacheDependency sc = new Web.Caching.SqlCacheDependency("ResourceSql", "tbl_ResourceSQL");
                System.Web.HttpContext.Current.Cache.Add("Rebuild_ResourceSet", _Rebuild_ResourceSet, sc, System.Web.Caching.Cache.NoAbsoluteExpiration, System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.Normal, onRemove);
            }
            catch(Exception )
            { 
                throw ;
            }
        }

        private void RemovedCallback(String k, Object v, System.Web.Caching.CacheItemRemovedReason r)
        {
            System.Diagnostics.Trace.WriteLine("RemovedCallback -> DataDB Changed. Cache Dismissed.");
        }

        /// <summary>
        /// Support funcion exec. stored with param
        /// </summary>
        /// <param name="StoredName">Stored procedure name to use for exstract data frm db</param>
        /// <param name="pms">List parameter used from stored</param>
        /// <returns></returns>
        private int execStored(string StoredName, List<SqlParameter> pms)
        {
            int _execStored = 0;
            System.Diagnostics.Trace.WriteLine("execStored -> Start");
            SqlConnection cn = new SqlConnection(_cnString);
            SqlCommand cm = new SqlCommand();

            cm.CommandText = StoredName;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Connection = cn;
            foreach (SqlParameter pm in pms)
            {
                cm.Parameters.Add(pm);
            }


            try
            {
                cn.Open();
            }
            catch (SqlException )
            {
                throw ;
            }
            
            try
            {
                _execStored = cm.ExecuteNonQuery();
            }
            catch (SqlException )
            {
                throw ;
            }
            finally
            {
                cn.Close();
            }

            System.Diagnostics.Trace.WriteLine("execStored -> End");
            return _execStored;
        }

        /// <summary>
        /// Generic function for get data from database.
        /// </summary>
        /// <param name="StoredName">Stored procedure name to use for exstract data frm db</param>
        /// <param name="pms">List parameter used from stored</param>
        /// <returns></returns>
        private DataTable getDataDt(string StoredName, List<SqlParameter> pms)
        {
            System.Diagnostics.Trace.WriteLine("getDataDt -> Get Data from DB");

            DataTable dt = new DataTable("myDT");
            dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
            SqlConnection cn = new SqlConnection(_cnString);
            SqlCommand cm = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();

            cm.CommandText = StoredName;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Connection = cn;
            da.SelectCommand = cm;
            foreach (SqlParameter pm in pms)
            {
                cm.Parameters.Add(pm);
            }

            try
            {
                cn.Open();
            }
            catch (SqlException )
            {
                throw ;
            }


            try
            {
                da.Fill(dt);
            }
            catch (SqlException )
            {
                throw ;
            }
            finally
            {
                cn.Close();
            }
            return dt;
        }
    }
}
